#include "widget.h"
#include "ui_widget.h"
#include <QSqlDatabase>
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlQuery>    // sql
#include <QVariantList>
#include <QStandardItemModel>
#include <QStringList>

/***************************************************
* Copyright 2020 caoshi@elaixin.cn
* @projectName   database_demo
* @brief         数据库(MySQL)基本操作
* @file          widget.cpp
* @author        caoshi
* @date          2020-11-08 17:40:52
****************************************************/

Widget::Widget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Widget)
{
    ui->setupUi(this);
    qDebug() << QSqlDatabase::drivers();
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("58.87.96.156");
    db.setPort(23306);
    db.setUserName("root");
    db.setPassword("hadoop");
    db.setDatabaseName("place");        // 使用的数据库

    if (!db.open()) {
        QMessageBox::warning(this, "打开数据库失败", db.lastError().text());
        return;
    }

    // 创建数据库表
//    QSqlQuery query;
//    query.exec("CREATE TABLE t_student "
//               "(id VARCHAR(64) PRIMARY KEY,"
//               "name VARCHAR(64) NOT NULL,"
//               "sex VARCHAR(3) NOT NULL,"
//               "age int(3) NOT NULL,"
//               "score int(3) NOT NULL)");

    // 插入数据
//    query.exec("insert into t_student "
//               "(id, name, sex, age, score) "
//               "values('1001', 'zhangsan', '男', 18, 100)");

//    query.exec("insert into t_student "
//               "(id, name, sex, age, score) "
//               "values('1002', 'lisi', '男', 19, 98)");

    // 批量插入 windoes 风格
//    query.prepare("insert into t_student (id, name, sex, age, score) values(?, ?, ?, ?, ?)");
//    // 给字段设置内容
//    QVariantList idList, nameList, sexList, ageList, scoreList;
//    idList << "1003" << "1004" << "1005" << "1006";
//    nameList << "wangwu" << "zhaoliu" << "kngming" << "liubei" << "guanyu";
//    sexList << "男" << "女" << "男" << "女";
//    ageList << 50 << 24 << 36 << 47;
//    scoreList << 98 << 78 << 60 << 90;
      // 给字段绑定, 要按顺序!!
//    query.addBindValue(idList);
//    query.addBindValue(nameList);
//    query.addBindValue(sexList);
//    query.addBindValue(ageList);
//    query.addBindValue(scoreList);
      // 执行命令
//    query.execBatch();

    // Oracle 风格
//    query.prepare("insert into t_student (id, name, sex, age, score) values(:id, :name, :sex, :age, :score)");

    // 给字段设置内容
//    QVariantList idList, nameList, sexList, ageList, scoreList;
//    idList << "1003" << "1004" << "1005" << "1006";
//    nameList << "wangwu" << "zhaoliu" << "kngming" << "liubei" << "guanyu";
//    sexList << "男" << "女" << "男" << "女";
//    ageList << 50 << 24 << 36 << 47;
//    scoreList << 98 << 78 << 60 << 90;

//    // 给字段绑定
//    query.bindValue(":name", nameList);
//    query.bindValue(":id", idList);
//    query.bindValue(":age", ageList);
//    query.bindValue(":score", scoreList);
//    query.bindValue(":sex", sexList);
//    query.execBatch();


}

Widget::~Widget()
{
    delete ui;
}

void Widget::on_btnDel_clicked()
{
    // 要删除学生的姓名
    QString name = ui->lineEdit->text();
    // sql语句
    QString delSql = QString("delete from t_student where name = '%1'").arg(name);

    // 开启事务
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.exec(delSql);
}

void Widget::on_btnSure_clicked()
{
    // 确定删除
    QSqlDatabase::database().commit();
}

void Widget::on_btnCancel_clicked()
{
    // 回滚,取消
    QSqlDatabase::database().rollback();
}

// 查询按钮
void Widget::on_btnSearch_clicked()
{
    // 初始化tableView
    QStandardItemModel *model = initTableView();

    // 获取查询输入框内容
    QString searchText = ui->searchLineEdit->text();
    // 查询sql语句
    QString selSql = QString("select * from t_student where name like '%1%'").arg(searchText);
    QSqlQuery query;
    query.exec(selSql);
    QStandardItem *item = NULL;
    int i = 0;

    while (query.next()) {
        // 取出当前行的内容
        item = new QStandardItem(query.value("id").toString());
        model->setItem(i, 0, item);
        item = new QStandardItem(query.value("name").toString());
        model->setItem(i, 1, item);
        item = new QStandardItem(query.value("sex").toString());
        model->setItem(i, 2, item);
        item = new QStandardItem(query.value("age").toString());
        model->setItem(i, 3, item);
        item = new QStandardItem(query.value("score").toString());
        model->setItem(i, 4, item);
        i++;
    }

    // 将查询结果展示在 line view中
    ui->stuList->setModel(model);
    ui->stuList->show();
}

/**
 * 初始化tableView
 * @brief Widget::initTableView
 * @return
 */
QStandardItemModel * Widget::initTableView() {
    // 表头
    QStandardItemModel *model = new QStandardItemModel();
    QStringList tabHead = QObject::trUtf8("ID,姓名,性别,年龄,成绩").simplified().split(",");
    model->setHorizontalHeaderLabels(tabHead);
//    ui->stuList->setModel(model);
    return model;
}


